import pandas as pd
import numpy as np

file = 'D:\\test.xlsx'
to_file = 'D:\\python\\test.xlsx'

df = pd.read_excel(file, sheet_name='Sheet1', header=None)
# df.drop(df.index[5],inplace=True)
print(df)
print(df.shape)
# print(df.dtypes)
print("\n\n\n")

def handleBlock(writer, rows):
    print("rows:",rows)
    if len(rows) < 2:
        print("参数长度不够!!")
        return
    block = pd.DataFrame(df[rows[0]:rows[1]])
    sheet_name = block.iloc[0, 0]
    print(type(block), block.shape[0], block.shape[1])
    # for i in range(block.shape[1] - 1, -1, -1):
    #     if "合计" in block.iloc[1, i]:
    #         block.iloc[:, i] = np.nan
    #     elif "分类" in block.iloc[1, i]:
    #         pass
    #     else:
    #         block.iloc[2, i] = block.iloc[2, i] * 1000
    data_ = block.iloc[2:, 1:]
    # 批量改变DF数据类型
    data_.astype(float)
    # 动态替换表格中的值
    # 类似的函数还有  where replace
    data_.mask(data_ > 0, data_ * 1000,inplace=True)
    # 用分片创建新的DF
    block_split = pd.DataFrame(block[1:3])
    # 设置列头
    block_split.columns = block_split.iloc[0]
    print(block_split)

    block_split.insert(0, '序号', ['序号', 1])
    block_split.insert(1, '楼层_构件', ['楼层_构件', sheet_name])
    block_split.insert(3, '相同层数', ['相同层数', 1])
    # block_split.drop(block_split.index[0], inplace=True)
    print(block_split)
    block_split.to_excel(writer, sheet_name=sheet_name, index=False, header=None)


with pd.ExcelWriter(to_file) as writer:
    for i in range(df.shape[0]):
        if i % 3 == 0:
            handleBlock(writer, [i, i + 3])

print('\n\n\n', df)
